﻿using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System;
using System.Drawing;
using System.Linq;
using System.Reflection;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
using CommonTools;

namespace shop.Web.Excel
{

    /// <summary> excel文件导出,导入,编辑工具, 基于epplus </summary>
    public class ExcelHelper:IDisposable
    {
        #region 属性, 字段
        

        /// <summary> 文件名 </summary>
        public string FileName { set; get; }
         

        /// <summary> 工作簿 </summary>
        public ExcelWorkbook Workbook { set; get; }


        /// <summary> 导出结果的excel表 </summary>
        public ExcelWorksheet CurrentSheet { set; get; }

        /// <summary> 数据导出到表格的设置 </summary>
        public List<SheetExportConfig> ExportConfigs { set; get; }

        #endregion


        #region 预置的单元格样式 

        /// <summary> 明细内容单元格样式, 有边框,文字居中,10号字体 </summary>
        public CellStyleStuck DetailCellStyle { set; get; }

        /// <summary> 明细内容(日期)单元格样式 有边框,文字居中,11号字体 </summary>
        public CellStyleStuck Date_DetailCellStyle { set; get; }

        /// <summary> 明细内容(double)单元格样式 有边框,文字居中,11号字体 </summary>
        public CellStyleStuck Double_DetailCellStyle { set; get; }

        /// <summary> 明细内容(int)单元格样式 有边框,文字居中,11号字体 </summary>
        public CellStyleStuck Int_DetailCellStyle { set; get; }

        /// <summary> 列标题内容单元格样式, 有边框,文字居中,10号字体 </summary>
        public CellStyleStuck ColumnCellStyle { set; get; }

        /// <summary> 末尾备注FooterText单元格样式, 有边框,文字居中,11号字体 </summary>
        public CellStyleStuck FooterCellStyle { set; get; }

        #endregion


        #region 初始化

        /// <summary> 初始化 </summary>
        public ExcelHelper()
        {
            //创建空文件
            this.CreateWorkbook("");
        }
        
        


        #endregion 


        #region 保存路径

        ///// <summary>
        ///// 打开窗口 选择excel文件保存路径,名称
        ///// </summary>
        ///// <param name="strFileName"></param>
        ///// <returns></returns>
        //public static string GetExcelName(string strFileName)
        //{
        //    //如果是一个完整的路径, 就不再询问了
        //    if (string.IsNullOrEmpty(strFileName)== false && strFileName.Contains(":") && strFileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
        //    {
        //        return strFileName;
        //    }

        //    //通过SaveFileDialog类弹出一个保存对话框
        //    SaveFileDialog sfd = new SaveFileDialog();

        //    //设置文件的保存类型，默认选中Excel文件
        //    sfd.Filter = "Excel文件|*.xlsx";

        //    ////设置默认保存文件名称
        //    sfd.FileName = strFileName;
        //    sfd.AddExtension = true;
        //    sfd.DefaultExt = "xlsx";

        //    //如果用户点击了保存对话框的确定按钮
        //    if (sfd.ShowDialog() == DialogResult.OK)
        //        strFileName = sfd.FileName;
        //    else
        //    {
        //        return null;
        //    }

        //    if (!string.IsNullOrEmpty(strFileName))
        //    {
        //        if (strFileName.ToLower().EndsWith("xlsx") == false)
        //            strFileName += ".xlsx";
        //    }
        //    else
        //    {
        //        return "";
        //    }

        //    if (File.Exists(strFileName) == true && FileHelper.FileIsUsed(strFileName) == true)
        //    {
        //        MessageBox.Show(Properties.Resources.filenamerepeat, Properties.Resources.systemInformation,
        //            MessageBoxButtons.OK, MessageBoxIcon.Information);
        //        return "";
        //    }

        //    return strFileName;

        //}

        #endregion


        #region 析构

        ///// <summary> 析构 </summary>
        //~ExcelHelper()
        //{
        //    //清理
        //    try
        //    {
        //        _pck?.Dispose();
        //    }
        //    catch (Exception )
        //    {
        //        //Console.WriteLine(e);
        //        //throw;
        //    }
        //}

        public void Dispose()
        {
            try
            {
                _pck?.Dispose();
            }
            catch (Exception)
            {
                //Console.WriteLine(e);
                //throw;
            }
        }

        #endregion


        #region 创建一个workbook, 初始化 需要调用这个方法

        private ExcelPackage _pck;

        /// <summary> 创建一个新的工作簿 </summary>
        private void CreateWorkbook(string bookFile)
        {
            if (_pck == null)
            {
                if (string.IsNullOrEmpty(bookFile))
                {
                    _pck = new ExcelPackage();
                }
                else
                {
                    //指定文件
                    using (FileStream file = new FileStream(bookFile, FileMode.Open, FileAccess.ReadWrite))
                    {
                        _pck = new ExcelPackage(file);
                    }
                }
            }


            this.Workbook = _pck.Workbook;


            #region 定义单元格样式


            //列标题样式 

            this.ColumnCellStyle = new CellStyleStuck
            {
                Font = new Font("Arial", 11f, FontStyle.Bold),
                HorizontalAlignment = ExcelHorizontalAlignment.Center,
                VerticalAlignment = ExcelVerticalAlignment.Center,
                BorderBottom = ExcelBorderStyle.Thin,
                BorderLeft = ExcelBorderStyle.Thin,
                BorderRight = ExcelBorderStyle.Thin,
                BorderTop = ExcelBorderStyle.Thin,
                BackColor =Color.FromArgb(0, 176, 240) ,
            };


            //明细内容单元格样式
            this.DetailCellStyle = new CellStyleStuck();
            DetailCellStyle.Font =new Font("宋体", 10.5f);
            DetailCellStyle.VerticalAlignment = ExcelVerticalAlignment.Center;
            DetailCellStyle.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            DetailCellStyle.BorderBottom = ExcelBorderStyle.Thin;
            DetailCellStyle.BorderLeft = ExcelBorderStyle.Thin;
            DetailCellStyle.BorderRight = ExcelBorderStyle.Thin;
            DetailCellStyle.BorderTop = ExcelBorderStyle.Thin;



            //日期单元格样式
            this.Date_DetailCellStyle = new CellStyleStuck();
            Date_DetailCellStyle.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            Date_DetailCellStyle.VerticalAlignment = ExcelVerticalAlignment.Center;
            Date_DetailCellStyle.BorderBottom = ExcelBorderStyle.Thin;
            Date_DetailCellStyle.BorderLeft= ExcelBorderStyle.Thin;
            Date_DetailCellStyle.BorderRight= ExcelBorderStyle.Thin;
            Date_DetailCellStyle.BorderTop = ExcelBorderStyle.Thin;
            Date_DetailCellStyle.Font= new Font("宋体", 10.5f);
            Date_DetailCellStyle.Numberformat = "yyyy-MM-dd";


            //int单元格样式
            this.Int_DetailCellStyle = new CellStyleStuck();
            Int_DetailCellStyle.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            Int_DetailCellStyle.VerticalAlignment = ExcelVerticalAlignment.Center;
            Int_DetailCellStyle.BorderBottom = ExcelBorderStyle.Thin;
            Int_DetailCellStyle.BorderLeft = ExcelBorderStyle.Thin;
            Int_DetailCellStyle.BorderRight= ExcelBorderStyle.Thin;
            Int_DetailCellStyle.BorderTop = ExcelBorderStyle.Thin;
            Int_DetailCellStyle.Font = new Font("宋体", 10.5f);
            Int_DetailCellStyle.Numberformat = "#,0.#######";


            //double单元格样式,保留两位
            this.Double_DetailCellStyle = new CellStyleStuck();
            Double_DetailCellStyle.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            Double_DetailCellStyle.VerticalAlignment = ExcelVerticalAlignment.Center;
            Double_DetailCellStyle.BorderBottom = ExcelBorderStyle.Thin;
            Double_DetailCellStyle.BorderLeft = ExcelBorderStyle.Thin;
            Double_DetailCellStyle.BorderRight = ExcelBorderStyle.Thin;
            Double_DetailCellStyle.BorderTop = ExcelBorderStyle.Thin;

            Double_DetailCellStyle.Font =new Font("宋体", 10.5f);
            Int_DetailCellStyle.Numberformat = "#,0.#######";



            //末尾备注 
            FooterCellStyle = new CellStyleStuck {Font = new Font("宋体", 9f)};

            #endregion


            #region 右击文件 属性信息

            {
                this.Workbook.Properties.Author = "";
                //DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

                //workbook.DocumentSummaryInformation = dsi;

                ////SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                //si.Author = "Java Pacific"; //填加xls文件作者信息
                //si.ApplicationName = "Java Pacific Soft"; //填加xls文件创建程序信息
                //si.LastAuthor = "" ;   //填加xls文件最后保存者信息
                //si.Comments = "说明信息"; //填加xls文件作者信息
                ////si.Title = "NPOI测试"; //填加xls文件标题信息
                ////si.Subject = "NPOI测试Demo";//填加文件主题信息
                //si.CreateDateTime = DateTime.Now;
                //workbook.SummaryInformation = si;
            }
            #endregion

        }

        #endregion


        #region SaveConfig

        /// <summary> 按照配置保存一个文件 </summary>
        public void SaveConfig(SheetExportConfig config )
        {
            #region  准备标题
            
            if (config.PrintColumnHeader && config.ColumnsTable == null && (config.ColumnsDict == null || config.ColumnsDict.Count ==0))
            {
                //自动从Table中生成列标题
                config.ColumnsDict = new Dictionary<string, string>();
                foreach (DataColumn item in config.Tables[0].Columns)
                    config.ColumnsDict.Add(item.ColumnName, string.IsNullOrEmpty(item.Caption) ? item.ColumnName : item.Caption);
            }

            #endregion

            //已经占用的行序号
            int rowIndex = Math.Max(1, config.BeginRowIndex);

            #region 表头及样式

            if (string.IsNullOrEmpty(config.Title ) == false)
            {
                config.Sheet.Cells[rowIndex, 1].Value = config.Title;
                config.Sheet.Row(rowIndex).Height = 21;

                //标题字体
                config.Sheet.Cells[rowIndex, 1].Style.Font.Size = 16f;
                config.Sheet.Cells[rowIndex, 1].Style.Font.Bold = true;
                config.Sheet.Cells[rowIndex, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                //合并单元格, 标题占用4列
                config.Sheet.Cells[rowIndex, 1, 1, 3].Merge = true;
                rowIndex++;
            }
            #endregion

            #region 填入副标题

            if (string.IsNullOrEmpty( config.SubTitle ) == false)
            {
                var cl = config.Tables[0].Columns.Count;
                config.Sheet.Cells[rowIndex, cl].Value = config.SubTitle;

                //标题字体
                config.Sheet.Cells[rowIndex, cl].Style.Font.Size = 12f;
                config.Sheet.Cells[rowIndex, cl].Style.Font.Bold = true;
                config.Sheet.Cells[rowIndex, cl].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                rowIndex++;
            }

            #endregion

            #region 填入明细的列标题

            if (config.ColumnsTable != null)  //有多行列标题的情况
            {
                config.ColumnsRowIndex = rowIndex;

                foreach (DataRow item in config.ColumnsTable.Rows)
                {
                    config.ColumnsRowIndex++;
                    int m = 1;
                    foreach (DataColumn col in config.ColumnsTable.Columns)
                    {
                        config.Sheet.Cells[config.ColumnsRowIndex, m].Value = item[col];
                        config.Sheet.Cells[config.ColumnsRowIndex, m].SetMyStyle(ColumnCellStyle);
                        m++;
                    }
                }
                rowIndex += config.ColumnsTable.Rows.Count;
            }
            else if(config.PrintColumnHeader)
            {
                config.ColumnsRowIndex = rowIndex; 

                int m = 1;
                foreach (var item in config.ColumnsDict.Values)
                {
                    config.Sheet.Cells[config.ColumnsRowIndex, m].Value = item;
                    config.Sheet.Cells[config.ColumnsRowIndex, m].SetMyStyle(ColumnCellStyle);
                    m++;
                }
                rowIndex++;
            }



            #endregion

            #region 填充内容
            
            int dataBegin = rowIndex;
            rowIndex = this.FillDataTables(config.Sheet, rowIndex, 1, config.Tables, config.Style);

            //单元格格式
            if (config.ColumnFormats != null)
            {
                foreach (var col in config.ColumnFormats.Keys)
                {
                    config.Sheet.Column(col).Style.Numberformat.Format = config.ColumnFormats[col];
                }
            }

            if (config.ColumnFormats2 != null)
            {
                foreach (var col in config.ColumnFormats2.Keys)
                {
                    int c = 0;
                    foreach (DataTable table in config.Tables)
                    {
                        if (table.Columns.Contains(col))
                        {
                            config.Sheet.Column(c).Style.Numberformat.Format = config.ColumnFormats2[col];
                            c++;
                            break;
                        }
                    }
                }
            }


            //数据格式, 边框
            config.Sheet.Cells[dataBegin, 1, Math.Max(dataBegin, rowIndex-1), config.Sheet.Dimension.Columns].SetMyStyle(DetailCellStyle);

            //调整列宽度
            AdjuestColumnWidth(config.Sheet);

            #endregion


            //填入末尾 
            if (!string.IsNullOrEmpty(config.FooterText))
            {
                config.Sheet.Cells[rowIndex, 1].Value = config.FooterText;
                config.Sheet.Cells[rowIndex, 1].SetMyStyle(this.FooterCellStyle);
                rowIndex++;
            }

            config.EndRowIndex = rowIndex;

            //打印缩放适合一个页面
            config.Sheet.PrinterSettings.FitToPage = false;
            config.Sheet.PrinterSettings.PaperSize = ePaperSize.A4;
            config.Sheet.PrinterSettings.RightMargin = 1;
            config.Sheet.PrinterSettings.LeftMargin = 1;
            config.Sheet.PrinterSettings.TopMargin = 1;
            config.Sheet.PrinterSettings.BottomMargin = 1;

            config.Sheet.HeaderFooter.FirstFooter.LeftAlignedText = "第 &P 页 / 共 &N 页";


            //打印缩放, 是一个0-100之间的数,  (默认合并到一页打印,这里需要修改)
            //sheet.PrintSetup.Scale = 100;

            //调用自定义方法
            config.AfterInsertDataAction?.Invoke(config);
        }

        /// <summary> 当数据是泛型列表时调用这个保存方法 </summary>
        public void SaveConfigGen<T>(SheetExportConfig config )
        {
            #region  准备标题

            bool hasColumnTitle = (config.ColumnsTable != null && config.ColumnsTable.Rows.Count>0) 
                                  || (config.ColumnsDict != null && config.ColumnsDict.Count > 0);

            #endregion

            //已经占用的行序号
            int rowIndex = Math.Max(1, config.BeginRowIndex);
            int titleRowIndex = 1;
            #region 表头及样式

            if (string.IsNullOrEmpty(config.Title) == false)
            {
                titleRowIndex = rowIndex;

                config.Sheet.Cells[rowIndex, 1].Value = config.Title;
                config.Sheet.Row(rowIndex).Height = 21;

                //标题字体
                config.Sheet.Cells[rowIndex, 1].Style.Font.Size = 16f;
                config.Sheet.Cells[rowIndex, 1].Style.Font.Bold = true;
                config.Sheet.Cells[rowIndex, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                ////合并单元格
                //config.Sheet.Cells[rowIndex, 1, rowIndex, 3].Merge = true;
                rowIndex++;
            }
            #endregion


            #region 填入副标题
            var subTitleRow = rowIndex;
            if (string.IsNullOrEmpty(config.SubTitle) == false)
            {
                //config.Sheet.Cells[rowIndex, 1].Value = config.SubTitle;

                ////标题字体
                //config.Sheet.Cells[rowIndex, 1].Style.Font.Size = 12f;
                //config.Sheet.Cells[rowIndex, 1].Style.Font.Bold = true;
                //config.Sheet.Cells[rowIndex, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                rowIndex++;
            }

            #endregion


            #region 列标题

            config.ColumnsRowIndex = rowIndex;

            if (hasColumnTitle)
            {
                if (config.ColumnsTable != null) //有多行列标题的情况
                {
                    rowIndex += config.ColumnsTable.Rows.Count;

                    foreach (DataRow item in config.ColumnsTable.Rows)
                    {
                        int m = 1;
                        foreach (DataColumn col in config.ColumnsTable.Columns)
                        {
                            config.Sheet.Cells[config.ColumnsRowIndex, m].Value = item[col];
                            config.Sheet.Cells[config.ColumnsRowIndex, m].SetMyStyle(ColumnCellStyle);
                            m++;
                        }
                        config.ColumnsRowIndex++;
                    }
                }
                else
                {
                    int m = 1;
                    foreach (var item in config.ColumnsDict.Values)
                    {
                        config.Sheet.Cells[config.ColumnsRowIndex, m].Value = item;
                        config.Sheet.Cells[config.ColumnsRowIndex, m].SetMyStyle(ColumnCellStyle);
                        m++;
                    }
                    rowIndex++;
                }
            } 


            #endregion


            #region 填充内容

            //var properties = typeof(T).GetProperties();

            //填入数据
            var data = config.DataList as IEnumerable<T>;
            var properties= typeof(T).GetProperties();
            if(properties.Length==0)
            {
                properties= TypeHelper.GetDataPropertyinfos<T>(data);
                if(config.Members == null || config.Members.Length==0)
                {
                    config.Members = properties;
                }
            }

            int rows = data.Count();
            int cs =1;  //列数
            if (config.MultyColumns > 1 )
            {
                //分栏
                if (config.Members != null && config.Members.Any())
                {
                    //行数
                    rows = data.Count() / config.MultyColumns + (data.Count() % config.MultyColumns ==0 ? 0 : 1);
                    //列数
                    cs = config.Members.Length+(config.BlankInMultyColumns ? 1 : 0);
                    
                    for (int i = 0; i < config.MultyColumns; i++)
                    {
                        config.Sheet.Cells[rowIndex, 1 + (i * cs)].LoadFromCollection(data.Skip(i*rows).Take(rows), 
                            !hasColumnTitle, config.Style, BindingFlags.Public | BindingFlags.Instance, config.Members);

                        //完成
                        if ((i + 1) * rows > data.Count())
                        {
                            break;
                        }
                    }
                   
                }
                else if (config.ColumnsDict != null && config.ColumnsDict.Count > 0 && (config.Members == null || config.Members.Any() == false))
                {
                    //指定了列，但是没有指定数据成员， 自动编辑成员信息
                    config.Members = new MemberInfo[config.ColumnsDict.Count];
                    int j = 0;
                    foreach (string key in config.ColumnsDict.Keys)
                    {
                        var p = properties.FirstOrDefault(a => a.Name == key);
                        if (p != null)
                        {
                            config.Members[j] = p;
                            j++;
                        }
                    }

                    try
                    {
                        //行数
                        rows = data.Count() / config.MultyColumns + (data.Count() % config.MultyColumns == 0 ? 0 : 1);
                        //列数
                        cs = config.Members.Length + (config.BlankInMultyColumns ? 1 : 0);

                        for (int i = 0; i < config.MultyColumns; i++)
                        {
                            config.Sheet.Cells[rowIndex, 1 + (i * cs )].LoadFromCollection(data.Skip(i * rows).Take(rows),
                                !hasColumnTitle, config.Style, BindingFlags.Public | BindingFlags.Instance, config.Members);

                            //完成
                            if ((i + 1) * rows > data.Count())
                            {
                                break;
                            }
                        }

                    }
                    catch (Exception)
                    {
                        throw;
                        //MessageBox.Show(e.Message);
                    }

                }
                else
                {
                    //config.Sheet.Cells[rowIndex, 1].LoadFromCollection(data, !hasColumnTitle, config.Style);

                    //行数
                    rows = data.Count() / config.MultyColumns + (data.Count() % config.MultyColumns == 0 ? 0 : 1);

                    //列数
                    cs = config.DataListType.GetProperties().Length + (config.BlankInMultyColumns ? 1 : 0); ;

                    for (int i = 0; i < config.MultyColumns; i++)
                    {
                        var destData = data.Skip(i * rows).Take(rows).ToList();

                        config.Sheet.Cells[rowIndex, 1+(i* cs)].LoadFromCollection( destData, !hasColumnTitle, config.Style );

                        //完成
                        if ((i + 1) * rows > data.Count())
                        {
                            break;
                        }
                    }

                }

                //正文标题居中
                config.Sheet.Cells[titleRowIndex, 1, titleRowIndex, config.MultyColumns * cs].Merge = true;
                config.Sheet.Cells[titleRowIndex, 1, titleRowIndex, config.MultyColumns * cs].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;


                // 方法解释 https://stackoverflow.com/questions/37902846/epplus-example-of-loadfromcollectiontienumerablet-boolean-tablestyles-b


                //数据格式, 边框
                for (int i = 0; i < config.MultyColumns; i++)
                {
                    config.Sheet.Cells[rowIndex, 1 + (i * cs ), rowIndex + rows, (i * cs)+ cs].SetMyStyle(DetailCellStyle);

                    if (!hasColumnTitle)
                    {
                        //列标题居中 
                        config.Sheet.Cells[rowIndex, 1 + (i * cs), rowIndex, (i * cs) + cs].SetMyStyle(ColumnCellStyle);
                    }
                }

            }
            else
            {
                //不分栏 写入数据
                if (config.Members != null && config.Members.Any())
                {
                    config.Sheet.Cells[rowIndex, 1].LoadFromCollection(data, !hasColumnTitle && config.PrintColumnHeader, config.Style,
                        BindingFlags.Public | BindingFlags.Instance, config.Members);
                    cs = config.Members.Length;
                }
                else if (config.ColumnsDict != null && config.ColumnsDict.Count > 0 && (config.Members == null || config.Members.Any() == false))
                {
                    //指定了列，但是没有指定数据成员， 自动编辑成员信息
                    config.Members = new MemberInfo[config.ColumnsDict.Count];
                    int i = 0;
                    foreach (string key in config.ColumnsDict.Keys)
                    {
                        var p = properties.FirstOrDefault(a => a.Name == key);
                        if (p != null)
                        {
                            config.Members[i] = p;
                        }
                        else
                        {
                            p = properties.FirstOrDefault(a => a.Name.Equals(key, StringComparison.OrdinalIgnoreCase));
                            if (p != null)
                            {
                                config.Members[i] = p;
                            }
                        }
                        i++;
                    }

                    cs = config.Members.Length; //列数

                    try                    
                    {
                        //todo 有问题
                        config.Sheet.Cells[rowIndex, 1].LoadFromCollection(data, !hasColumnTitle && config.PrintColumnHeader, config.Style,
                            BindingFlags.Public | BindingFlags.Instance, config.Members);
                    }
                    catch (Exception )
                    {
                        //MessageBox.Show(e.Message);
                    }
                     
                }
                else
                {
                    config.Sheet.Cells[rowIndex, 1].LoadFromCollection(data, !hasColumnTitle && config.PrintColumnHeader
                        , config.Style);

                    cs = config.Sheet.Dimension.Columns;
                }

                // 方法解释 https://stackoverflow.com/questions/37902846/epplus-example-of-loadfromcollectiontienumerablet-boolean-tablestyles-b

                if (!hasColumnTitle && config.PrintColumnHeader)
                {
                    //列标题居中 
                    config.Sheet.Cells[rowIndex, 1, rowIndex, config.Sheet.Dimension.Columns].SetMyStyle(ColumnCellStyle);
                    rowIndex++; //标题加一行
                }

                //数据格式, 边框
                config.Sheet.Cells[rowIndex, 1, rowIndex + rows, config.Sheet.Dimension.Columns].SetMyStyle(DetailCellStyle);


                //正文标题居中
                if(string.IsNullOrEmpty(config.Title) == false)
                {
                    config.Sheet.Cells[titleRowIndex, 1, titleRowIndex, cs].Merge = true;
                    config.Sheet.Cells[titleRowIndex, 1, titleRowIndex, cs].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
            }





            //数据格式, 边框
            ////int rows = data.Count();
            //config.Sheet.Cells[rowIndex, 1, rowIndex+ rows, config.Sheet.Dimension.Columns].SetMyStyle(DetailCellStyle);

            rowIndex += rows;

            for (int c = 0; c < config.MultyColumns; c++)
            {
                //需要处理日期格式
                for (int i = 0; i < properties.Length; i++)
                {
                    if (properties[i].PropertyType == typeof(DateTime) || properties[i].PropertyType == typeof(DateTime?))
                    {
                        //
                        //config.Sheet.Column(i + 1).Style.Numberformat.Format = "yyyy-MM-dd";
                        config.Sheet.Column(i + 1+(c*cs)).Style.Numberformat.Format = "yyyy-MM-dd";
                    }
                }

                //单元格格式
                if (config.ColumnFormats != null)
                {
                    foreach (var col in config.ColumnFormats.Keys)
                    {
                        //config.Sheet.Column(col ).Style.Numberformat.Format = config.ColumnFormats[col];
                        config.Sheet.Column(col + (c *cs)).Style.Numberformat.Format = config.ColumnFormats[col];
                    }
                }


                if (config.ColumnFormats2 != null)
                {
                    IEnumerable<MemberInfo> infos = null;
                    if (config.Members != null & config.Members.Any())
                        infos = config.Members;
                    else
                        infos = properties;

                    foreach (var col in config.ColumnFormats2.Keys)
                    {
                        int n = 0;
                        foreach (var info in infos)
                        {
                            if (info.Name == col)
                            {
                                //config.Sheet.Column(n).Style.Numberformat.Format = config.ColumnFormats2[col];
                                config.Sheet.Column(n+ (c * cs )).Style.Numberformat.Format = config.ColumnFormats2[col];
                                break;
                            }
                            n++;
                        }
                        break;

                    }
                }

            }


            config.Cs = cs;


            //调整列宽度
            AdjuestColumnWidth(config.Sheet);

            #endregion


            #region 数据跨列 合并单元格
            if (config.SpanColumns != null )
            {
                for (int i = config.ColumnsRowIndex; i <= rowIndex; i++)
                {
                    foreach (var item in config.SpanColumns)
                    {
                        config.Sheet.Cells[i, item.Key + 1,i, item.Key+ item.Value].Merge = true;
                    }
                }
            }

            #endregion


            #region 副标题

            if (string.IsNullOrEmpty(config.SubTitle) == false)
            {
                var subTitleRng = config.Sheet.Cells[subTitleRow, config.Cs];
                subTitleRng.Value = config.SubTitle;

                //标题字体
                subTitleRng.Style.Font.Size = 12f;
                subTitleRng.Style.Font.Bold = true;
                subTitleRng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
            }

            #endregion

            //填入末尾 
            if (!string.IsNullOrEmpty(config.FooterText))
            {
                config.Sheet.Cells[rowIndex, 1].Value = config.FooterText;
                config.Sheet.Cells[rowIndex, 1].SetMyStyle(this.FooterCellStyle);
                rowIndex++;
            }

            //打印缩放适合一个页面
            config.Sheet.PrinterSettings.FitToPage = false;
            config.Sheet.PrinterSettings.PaperSize = ePaperSize.A4;
            config.Sheet.PrinterSettings.RightMargin = 1;
            config.Sheet.PrinterSettings.LeftMargin = 1;
            config.Sheet.PrinterSettings.TopMargin = 1;
            config.Sheet.PrinterSettings.BottomMargin = 1;

            config.Sheet.HeaderFooter.FirstFooter.LeftAlignedText = "第 &P 页 / 共 &N 页";

            //最后一行
            config.EndRowIndex = rowIndex-1;

            //调用自定义方法
            config.AfterInsertDataAction?.Invoke(config);
            
        }

        #endregion


        #region 数据导出到Excel基本方法


        private int FillDataTables(ExcelWorksheet sheet, int beginRow, int beginCol, IEnumerable<DataTable> tables,
            TableStyles styles)
        {
            if (tables == null)
            {
                return beginRow+1;
            }

            int row = beginRow;
            foreach (DataTable table in tables)
            {
                row= FillDataTable(sheet, row, beginCol, table, styles);
            }

            return row;
        }

        /// <summary> 填充数据 </summary>
        /// <return>返回最后一行的行号</return>
        private int FillDataTable(ExcelWorksheet sheet, int beginRow, int beginCol, DataTable table, TableStyles styles)
        {
            if (table == null || table.Rows.Count ==0)
                return beginRow;

            var rng= sheet.Cells[beginRow, beginCol].LoadFromDataTable(table, false, styles);

            //导入datatable时  表格会生成 Tables, 将此转换成普通区域
            var tab = sheet.Tables.GetFromRange(rng);
            if (tab != null)
            {
                sheet.Tables.Delete(tab, false);  //清楚生成的table对象
            }

            //设置格式
            int c = beginCol;
            foreach (DataColumn column in table.Columns)
            {
                //Namespace 存储列的自定义格式
                if (!string.IsNullOrEmpty(column.Namespace))
                {
                    sheet.Column(c).Style.Numberformat.Format = column.Namespace;
                }
                else if (column.DataType == typeof(DateTime))
                {
                    sheet.Column(c).Style.Numberformat.Format = "yyyy-MM-dd";
                }
                else if (column.DataType == typeof(decimal) || column.DataType == typeof(double) || column.DataType == typeof(float))
                {
                    //sheet.Column(c).Style.Numberformat.Format = "#,0.00###";
                }
                //else if (column.DataType == typeof(int) || column.DataType == typeof(short) || column.DataType == typeof(long))
                //{
                //    sheet.Column(c).Style.Numberformat.Format = "#,0.00###";
                //}

                c++;
            }
            return beginRow + table.Rows.Count;
        }

        /// <summary> 填充数据 </summary>
        /// <return>返回最后一行的行号</return>
        private int FillData(ExcelWorksheet sheet, int beginRow, int beginCol, DataTable table)
        {
            //开始遍历
            int rowIndex = beginRow;

            foreach (DataRow row in table.Rows)
            {
                rowIndex++;

                foreach (DataColumn column in table.Columns)
                {
                    ExcelRange newCell = this.CurrentSheet.Cells[rowIndex, column.Ordinal+ beginCol];
                    newCell.SetMyStyle(this.DetailCellStyle);

                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                        case "System.String":   //字符串类型
                            newCell.Value = drValue;
                            break;
                        case "System.DateTime":     //日期类型
                            if (string.IsNullOrEmpty(drValue))
                            {
                                newCell.SetMyStyle(this.Date_DetailCellStyle);
                            }
                            else
                            {
                                DateTime.TryParse(drValue, out var dateV);
                                newCell.Value = dateV;
                                newCell.SetMyStyle(this.Date_DetailCellStyle);//格式化显示
                            }
                            break;
                        case "System.Boolean"://布尔型
                            bool.TryParse(drValue, out var boolV);
                            newCell.Value = boolV;
                            newCell.SetMyStyle(this.DetailCellStyle);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int.TryParse(drValue, out var intV);
                            newCell.Value = intV;
                            newCell.SetMyStyle(this.Int_DetailCellStyle);
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            decimal.TryParse(drValue, out var doubV);
                            newCell.Value = doubV;
                            newCell.SetMyStyle(this.Double_DetailCellStyle);
                            break;
                        case "System.DBNull"://空值处理
                            newCell.Value = "";
                            break;
                        default:
                            newCell.Value = drValue;
                            newCell.SetMyStyle(this.DetailCellStyle);
                            break;
                    }

                }
            }

            return rowIndex;
        }


        /// <summary>
        /// 重新调整列宽
        /// </summary>
        /// <param name="sheet"></param>
        public static void AdjuestColumnWidth(ExcelWorksheet sheet)
        {
            //依据列名称计算宽度

            //int[] arrColWidth = new int[this.ColumnsDict.Count];

            //int k = 0;
            //foreach (string item in this.ColumnsDict.Values)
            //{
            //    arrColWidth[k] = Encoding.GetEncoding(936).GetBytes(item).Length;
            //    k++;
            //}

            ////遍历 数据前50行
            //for (int i = 0; i < this.ValueTable.Rows.Count && i < 50; i++)
            //{
            //    for (int j = 0; j < this.ValueTable.Columns.Count ; j++)
            //    {
            //        int intTemp = Encoding.GetEncoding(936).GetBytes(this.ValueTable.Rows[i][j].IsNull()).Length;
            //        if (intTemp > arrColWidth[j])
            //            arrColWidth[j] = intTemp;
            //    }
            //}

            ////改变列宽
            //for (int i = 0; i < this.ColumnsDict.Count; i++)
            //{
            //    sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
            //}

            //自动调整列宽, 最小3, 最大50, 单位与excel中单位一直 
            sheet.Cells[sheet.Dimension.Address].AutoFitColumns(3,50);  //
            

        }


        #endregion


        /// <summary> 保存到文件 </summary>
        public string SaveFile(string strFileName)
        {
            //得到一个文件名
            //strFileName = ExcelHelper.GetExcelName(strFileName);

            if (string.IsNullOrEmpty(strFileName))
            {
                return null;
            }

            if (strFileName.ToLower().EndsWith("xlsx") == false)
            {
                strFileName += ".xlsx";
            }

            FileInfo fi = new FileInfo(strFileName);

            try
            {
                this._pck.SaveAs(fi);
            }
            catch (Exception )
            {
                throw ;
            }

            return strFileName;
        }
         



        #region 其它方法

        private static Dictionary<string, string> ListToDictionary(List<string> list)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            foreach (string item in list)
                dict.Add(item, item);

            return dict;
        }


        /// <summary>
        /// 打开文件
        /// </summary>
        public void OpenFile()
        {
            if (!string.IsNullOrEmpty(this.FileName))
                System.Diagnostics.Process.Start(this.FileName);
        }

        #endregion



        #region 读取excel 


        /// <summary>
        /// 读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <param name="sheetName">表格名称</param>
        /// <param name="includeBlankRow">是否包含空行, 默认包含</param>
        /// <param name="showForm">显示窗体</param>
        /// <param name="titleRow">列标题所在行, 默认第1行, 小于等于0则不读取列标题</param>
        /// <param name="dataBegin">数据起始行, 默认第2行(第一行是列标题)</param>
        /// <returns></returns>
        public static DataTable Import(string strFileName,string sheetName ="", int titleRow =1, int dataBegin =2,
            bool includeBlankRow = true, bool showForm= true)
        {
            //if (string.IsNullOrEmpty(strFileName))
            //{
            //    var openDialog = new OpenFileDialog()
            //    {
            //        Filter = "Excel文件|*.xlsx;*.xls;*.xlsm",
            //        Title = "打开表格文件",
            //        CheckFileExists = true,
            //    };

            //    if (openDialog.ShowDialog() != DialogResult.OK)
            //    {
            //        return null;
            //    }

            //    strFileName = openDialog.FileName;
            //}

            string ext = Path.GetExtension(strFileName);
            if (string.IsNullOrEmpty(ext))
            {
                return null;
            }

            if (ext.EndsWith(".xlsx") == false && ext.EndsWith(".xls") && ext.EndsWith(".xlsm"))
            {
                return null;
            }

            try
            {
                using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    using (ExcelPackage pck = new ExcelPackage(file))
                    {
                        ExcelWorkbook workbook = pck.Workbook;

                        if (workbook == null || workbook.Worksheets.Count == 0)
                        {
                            return null;  //没有表格
                        }

                        ExcelWorksheet sheet;
                        //if ( showForm )
                        //{
                        //    //打开选择sheet功能窗体
                        //    WorkSheetPicker picker = new WorkSheetPicker(workbook, strFileName);
                        //    if (picker.ShowDialog() == DialogResult.OK)
                        //    {
                        //        sheet = picker.Sheet;
                        //        titleRow = picker.TitleRow;
                        //        dataBegin = picker.BeginRow;
                        //    }
                        //    else
                        //    {
                        //        sheet = null;
                        //    }
                        //}
                        //else
                        {
                            if (string.IsNullOrEmpty(sheetName))
                            {
                                sheet = workbook.Worksheets.FirstOrDefault();
                            }
                            else
                            {
                                sheet = workbook.Worksheets.FirstOrDefault(a => a.Name == sheetName);
                            }

                            //不能按名称找到,就将sheetname转换成数字,查找一下
                            if (sheet == null && int.TryParse(sheetName, out int index))
                            {
                                if (workbook.Worksheets.Count > index)
                                {
                                    sheet = workbook.Worksheets[index];
                                }
                            }
                        }


                        if (sheet == null)
                        {
                            return null;   //找不到表格了
                        }

                        //这里开始读取文件
                        return WorksheetToTable(sheet,titleRow, dataBegin,includeBlankRow);
                    }
                }
            }
            catch (Exception)
            {
                //MessageBox.Show(e.Message, Resources.systemInformation, MessageBoxButtons.OK,
                //    MessageBoxIcon.Information);
                throw;
                //return null;
            }
            

        }



        /// <summary>
        /// worksheet 转datatable 
        /// 第一行作为列名称
        /// </summary>
        /// <param name="worksheet"></param>
        /// <param name="includeBlankRow">是否包含空行, 默认包含</param>
        /// <param name="titleRow">列标题所在行, 默认第1行, 小于等于0则不读取列标题</param>
        /// <param name="dataBegin">数据起始行, 默认第2行(第一行是列标题)</param>
        /// <param name="maxRows">最多提取的行数, 默认所有行</param>
        /// <returns></returns>
        public static DataTable WorksheetToTable(ExcelWorksheet worksheet, int titleRow = 1, int dataBegin = 2,
            bool includeBlankRow = true, int maxRows=0 )
        {
            //获取worksheet的行数
            int rows = (worksheet.Dimension?.End?.Row ?? 0);
            if (maxRows > 0 && rows > maxRows)
            {
                rows = maxRows; //限制了最大行数
            }


            //获取worksheet的列数 
            int cols = Math.Min(200, worksheet.Dimension?.End?.Column ?? 0 );
            DataTable dt = new DataTable(worksheet.Name);
            DataRow dr = null;

            //遍历列
            if (titleRow >=1)
            {
                for (int i = 1; i <= cols; i++)
                {
                    string name = worksheet.Cells[titleRow, i].Value?.ToString();
                    if (string.IsNullOrEmpty(name))
                    {
                        dt.Columns.Add("列" + i);
                    }
                    else if (dt.Columns.Contains(name))
                    {
                        dt.Columns.Add(name + i);
                    }
                    else
                    {
                        dt.Columns.Add(name);
                    }
                }
            }
            else
            {   //没有指定列的所在行
                for (int i = 1; i <= cols; i++)
                {
                    dt.Columns.Add("列" + i);
                }
            }
            

            //遍历数据
            int errCount = 0;
            for (int i = dataBegin; i <= rows; i++)
            {
                if (i >= 1)
                {
                    dr = dt.Rows.Add();
                }

                for (int j = 1; j <= cols; j++)
                {
                    try
                    {
                        dr[j - 1] = worksheet.Cells[i, j].Value?.ToString();
                    }
                    catch (Exception )
                    {
                        errCount++;
                        //MessageBox.Show(ex.Message);
                        if (errCount > 5)
                        {
                            break;  //错误次数太多, 退出循环
                        }
                    }
                }
            }
            return dt;
        }

        #endregion


        #region 简单读取内容

        /// <summary> 简单读取内容, 行列从1开始计数 </summary>
        public static object[,] ReadCells(string strFileName, string sheetName , int beginRow, int rowCount, int beginCol , int colCount)
        {
            string ext = Path.GetExtension(strFileName);
            if (string.IsNullOrEmpty(ext))
            {
                return null;
            }

            if (ext.EndsWith(".xlsx") == false && ext.EndsWith(".xls") && ext.EndsWith(".xlsm"))
            {
                return null;
            }

            try
            {
                using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                {
                    using (ExcelPackage pck = new ExcelPackage(file))
                    {
                        ExcelWorkbook workbook = pck.Workbook;

                        if (workbook == null || workbook.Worksheets.Count == 0)
                        {
                            return null;  //没有表格
                        }

                        ExcelWorksheet sheet;
    
                        if (string.IsNullOrEmpty(sheetName))
                        {
                            sheet = workbook.Worksheets.FirstOrDefault();
                        }
                        else
                        {
                            sheet = workbook.Worksheets.FirstOrDefault(a => a.Name == sheetName);
                        }

                        //不能按名称找到,就将sheetname转换成数字,查找一下
                        if (sheet == null && int.TryParse(sheetName, out int index))
                        {
                            if (workbook.Worksheets.Count > index)
                            {
                                sheet = workbook.Worksheets[index];
                            }
                        }


                        if (sheet == null)
                        {
                            return null;   //找不到表格了
                        }

                        //这里开始读取文件
                        //遍历数据
                        int errCount = 0;
                        object[,] arr = new object[rowCount, colCount];
                        
                        for (int i = beginRow; i < beginRow+ rowCount ; i++)
                        { 
                            for (int j = beginCol; j < beginCol+ colCount; j++)
                            {
                                try
                                {
                                    arr[i-beginRow, j -beginCol] = sheet.Cells[i, j].Value;
                                }
                                catch (Exception )
                                {
                                    errCount++;
                                    //MessageBox.Show(ex.Message);
                                    if (errCount > 5)
                                    {
                                        break;  //错误次数太多, 退出循环
                                    }
                                }
                            }
                        }

                        return arr;
                    }
                }
            }
            catch (Exception )
            {
                //    MessageBox.Show(e.Message, Resources.systemInformation, MessageBoxButtons.OK,
                //        MessageBoxIcon.Information);
                throw;
                //return null;
            }


        }



        public static IEnumerable<string> GetAllSheetNames(string strFileName)
        {
            if (string.IsNullOrEmpty(strFileName) || File.Exists(strFileName) == false)
                return new List<string>();


            try
            {
                using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                {
                    using (ExcelPackage pck = new ExcelPackage(file))
                    {
                        ExcelWorkbook workbook = pck.Workbook;

                        if (workbook == null || workbook.Worksheets.Count == 0)
                        {
                            return new List<string>(); ;  //没有表格
                        }

                        return workbook.Worksheets.Select(a => a.Name).ToArray();
                    }
                }
            }
            catch (Exception )
            {
                throw;
                //MessageBox.Show(e.Message, Resources.systemInformation, MessageBoxButtons.OK,
                //    MessageBoxIcon.Information);
                //return null;
            }

        }


        #endregion


        #region 使用 yield 读取excel

        /// <summary> 打开界面 让用户选择表格文件, 返回读取内容的配置 </summary>
        public SheetReadConfig GetReadConfig(string strFileName = null, string sheetName = null,
            int titleRow = 1, int dataBegin = 2, bool showForm = true)
        {
            var config = new SheetReadConfig()
            {
                ExcelFile= strFileName, SheetName=sheetName, TitleRow= titleRow, DataBegin=dataBegin, ShowForm=showForm,
            };


            //if (string.IsNullOrEmpty(strFileName))
            //{
            //    var openDialog = new OpenFileDialog()
            //    {
            //        Filter = "Excel文件|*.xlsx;*.xls;*.xlsm",
            //        Title = "打开表格文件",
            //        CheckFileExists = true,
            //    };

            //    if (openDialog.ShowDialog() != DialogResult.OK)
            //    {
            //        config.Stop = true;
            //        return config;
            //    }
            //    config.ExcelFile = openDialog.FileName;
            //}

            string ext = Path.GetExtension(config.ExcelFile);
            if (string.IsNullOrEmpty(ext))
            {
                config.Stop = true;
                return config;
            }

            if (ext.EndsWith(".xlsx") == false && ext.EndsWith(".xls") && ext.EndsWith(".xlsm"))
            {
                config.Stop = true;
                return config;
            }

            using (FileStream file = new FileStream(config.ExcelFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                _pck = new ExcelPackage(file);


                ExcelWorkbook workbook = _pck.Workbook;

                if (workbook == null || workbook.Worksheets.Count == 0)
                {
                    config.Stop = true;
                    return config;
                }

                //ExcelWorksheet sheet;
                //if (showForm)
                //{
                //    //打开选择sheet功能窗体
                //    WorkSheetPicker picker = new WorkSheetPicker(workbook, config.ExcelFile);
                //    if (picker.ShowDialog() == DialogResult.OK)
                //    {
                //        config.SheetName = picker.Sheet.Name;
                //        config.TitleRow = picker.TitleRow;
                //        config.DataBegin = picker.BeginRow;
                //    }
                //    else
                //    {
                //        //sheet = null;
                //        config.SheetName = null;
                //    }
                //}
                //else
                {
                    config.SheetName = string.IsNullOrEmpty(sheetName)
                        ? workbook.Worksheets.FirstOrDefault()?.Name
                        : sheetName;

                    //不能按名称找到,就将sheetname转换成数字,查找一下
                    if (string.IsNullOrEmpty(config.SheetName)  && int.TryParse(sheetName, out int index))
                    {
                        if (workbook.Worksheets.Count > index)
                        {
                            config.SheetName = workbook.Worksheets[index].Name;
                        }
                    }
                }

                if (string.IsNullOrEmpty(config.SheetName))
                {
                    config.Stop = true;
                    return config;
                }
            }

            return config;
        }


        public IEnumerable<object[]> ReadSheet(SheetReadConfig config)
        {

            ExcelWorkbook workbook = _pck.Workbook;

            var sheet = workbook.Worksheets[config.SheetName];

            //获取worksheet的行数
            int rows = (sheet.Dimension?.End?.Row ?? 0);


            //获取worksheet的列数 
            int cols = Math.Min(200, sheet.Dimension?.End?.Column ?? 0);

            //遍历数据
            for (int i = config.DataBegin; i <= rows; i++)
            {
                var values = new object[cols];

                for (int j = 1; j <= cols; j++)
                {
                    values[j - 1] = sheet.GetValue(i, j);   //读取一个单元格
                }

                yield return values;
            }
                
        }


        /// <summary>
        /// worksheet 转datatable 
        /// 第一行作为列名称
        /// </summary>
        /// <param name="worksheet"></param>
        /// <param name="includeBlankRow">是否包含空行, 默认包含</param>
        /// <param name="titleRow">列标题所在行, 默认第1行, 小于等于0则不读取列标题</param>
        /// <param name="dataBegin">数据起始行, 默认第2行(第一行是列标题)</param>
        /// <param name="maxRows">最多提取的行数, 默认所有行</param>
        /// <returns></returns>
        public IEnumerable<object[]> ReadSheet(ExcelWorksheet worksheet, int titleRow = 1, int dataBegin = 2,
            bool includeBlankRow = true, int maxRows = 0)
        {
            //获取worksheet的行数
            int rows = (worksheet.Dimension?.End?.Row ?? 0);
            if (maxRows > 0 && rows > maxRows)
            {
                rows = maxRows; //限制了最大行数
            }


            //获取worksheet的列数 
            int cols = Math.Min(200, worksheet.Dimension?.End?.Column ?? 0);

            //遍历数据
            //int errCount = 0;
            for (int i = dataBegin; i <= rows; i++)
            {
                var values = new object[cols];

                for (int j = 1; j <= cols; j++)
                {
                    //try
                    //{
                        values[j - 1] = worksheet.GetValue(i, j);   //读取一个单元格
                    //}
                    //catch (Exception ex)
                    //{
                    //    errCount++;
                    //    MessageBox.Show(ex.Message);
                    //    if (errCount > 5)
                    //    {
                    //        break;  //错误次数太多, 退出循环
                    //    }
                    //}
                }

                yield return values;
            }
            //return dt;
        }


        #endregion

    }

}